<?php
/**
 * Created by PhpStorm.
 * User: lenovo
 * Date: 2020/9/24
 * Time: 20:05
 */

namespace app\admin\controller;
use app\admin\model\CourseModel;
use app\admin\model\GradeModel;
use app\admin\model\RoomModel;
use app\admin\model\SchoolModel;
use app\admin\model\StudentModel;
use app\admin\model\TeacherModel;
use cmf\controller\AdminBaseController;
use think\Db;
use think\db\Query;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class GradeController extends AdminBaseController
{
    public function index(){
        $name = input('name');
        $data = Db::name('grade')
            ->where(function (Query $query) use ($name){
                if ($name){
                    $query->where('ji_grade.name','like',"%$name%");
                }
            })
            ->join('ji_course','ji_course.id=course_id')
            ->join('ji_student','ji_student.id=student_id')
            ->field('ji_grade.*')
            ->field('ji_course.name as course_name')
            ->field('ji_student.name as student_name')
            ->order('id desc')
            ->paginate(3,false,$this->request->param());
        $page = $data->render();
        return $this->fetch('',['grade'=>$data,'page'=>$page]);
    }
    public function add()
    {
        //查询学校表的数据
        $school=SchoolModel::select();
        $course=CourseModel::select();
        //渲染添加页面，接收返回的值
        return view('add',['school'=>$school,'course'=>$course]);
    }
    public function addPost()
    {
        $data = input();
        $data['create_time'] = date('Y-m-d H:i:s',time());
        $add=GradeModel::create($data);
        //判断添加是否成功
        if ($add){
            //设置成功后跳转页面的地址，成功提示且跳转
            $this->success('添加成功','grade/index');
        }else{
            //错误页面的默认跳转页面是返回前一页，失败，提示
            $this->error('添加失败');
        }
    }

    public function getRoomBySchool()
    {
        //获取当前school_id
        $schoolId = input('school_id');
        //连接班级表，查询school_id的值
        $rooms = RoomModel::where('school_id',$schoolId)->select();
        //计算$rooms的条数
        $num = count($rooms);
        //如果$rooms的条数大于0
        if ($num > 0) {
            //定义$data，
            $data=["<option value=''>请选择班级</option>"];
            //foreach循环
            foreach ($rooms as $v){
                //循环出当前school_id里的所有班级名称
                $opt = "<option value='".$v->id."'>".$v->name."</option>";
                //显示$data和foreach循环的数据
                array_push($data,$opt);
            }
            //返回$data
            return $data;
        }else{
            //否者返回暂无数据
            return"<option value=''>暂无数据</option>";
        }
    }

    public function getStudentByRoom()
    {
        //获取当前room_id
        $roomId = input('room_id');
        //连接学生表，查询room_id的值
        $student = StudentModel::where('room_id',$roomId)->select();
        //判断是否选中班级的id
        if ($student){
            $data=["<option value=''>请选择学生</option>"];
            //foreach循环输出
            foreach ($student as $v){
                //循环出当前room_id里的所有学生名称
                $opt = "<option value='".$v->id."'>".$v->name."</option>";
                //按照数组的顺序显示$data和foreach循环的数据
                array_push($data,$opt);
            }
            //返回$data
            return $data;
        }else{
            //返回暂无数据
            return"<option value=''>暂无数据</option>";
        }
    }
    public function edit()
    {
        $id = input('id');
        $grade = GradeModel::get($id);
        $school = SchoolModel::all();
        $course=CourseModel::select();
        $room_id = StudentModel::where('id', $grade->student_id)->value('room_id');
        $school_id = RoomModel::where('id', $room_id)->value('school_id');
        $room = RoomModel::where('school_id', $school_id)->select();
        $student = StudentModel::where('room_id', $room_id)->select();
        return $this->fetch('', [
            'data' => $grade,
            'school' => $school,
            'room_id' => $room_id,
            'school_id' => $school_id,
            'room' => $room,
            'student' => $student,
            'course'=>$course
        ]);
    }
    public function editPost()
    {
        //获取edit.html提交过来的值
        $data = input();
        //从数据库获取这条数据的id，并更新数据
        $edit=GradeModel::update($data,['id'=>$data['id']]);
        //获取是否编辑成功
        if ($edit){
            //设置成功后跳转页面的地址，成功提示且跳转
            $this->success('修改成功','grade/index');
        }else{
            //错误页面的默认跳转页面是返回前一页，失败，提示
            $this->error('修改失败');
        }
    }
    public function delete()
    {
        //获取要删除的数据的id
        $id=input('id');
        //删除在数据库积分管理表获取的id的数据
        $del=GradeModel::destroy($id);
        //判断是否删除成功
        if ($del){
            //成功，提示，跳转
            $this->success("删除成功！", url("grade/index"));
        }else{
            //失败，提示
            $this->error("删除失败！");
        }
    }
    public function exportExcel()
    {
        // 查询要导出的数据
        $data = Db::name('grade')
            ->join('ji_course','ji_course.id=course_id')
            ->join('ji_student','ji_student.id=student_id')
            ->field('ji_grade.*')
            ->field('ji_course.name as course_name')
            ->field('ji_student.name as student_name')
            ->select();
        // 实例化
        $spreadsheet = new Spreadsheet();
        // 获取活动单元格
        $sheet = $spreadsheet->getActiveSheet();
        // 获取单元格
        $cellA = $sheet->getCell('A1');
        // 设置单元格的值
        $cellA->setValue('ID');
        // 设置 A 列 列宽
        $sheet->getColumnDimension('A')->setWidth(10);
        // 设置第一行 行高
        $sheet->getRowDimension(1)->setRowHeight(20);

        $cellB = $sheet->getCell('B1');
        $cellB->setValue('学生姓名');
        $sheet->getColumnDimension('B')->setWidth(10);

        $cellC = $sheet->getCell('C1');
        $cellC->setValue('科目名称');
        $sheet->getColumnDimension('C')->setWidth(10);

        $cellD = $sheet->getCell('D1');
        $cellD->setValue('成绩');
        $sheet->getColumnDimension('D')->setWidth(20);


        // 设置样式 标题
        $styleArray = [
            'alignment' => [
                'horizontal' => 'center', //水平居中
                'vertical' => 'center', //垂直居中
            ],
            'font' => [
                'name' => '黑体',
                'bold' => false,
                'size' => 10
            ]
        ];
        // 设置样式 正文
        $styleArrayBody = [
            'alignment' => [
                'horizontal' => 'center', //水平居中
                'vertical' => 'center', //垂直居中
            ],
            'font' => [
                'name' => '宋体',
                'bold' => false,
                'size' => 10
            ]
        ];
        // 应用样式
        $sheet->getStyle('A1')->applyFromArray($styleArray);
        $sheet->getStyle('B1')->applyFromArray($styleArray);
        $sheet->getStyle('C1')->applyFromArray($styleArray);
        $sheet->getStyle('D1')->applyFromArray($styleArray);

        // 从 A2 开始填充数据
        foreach ($data as $k => $v) {
            $n = $k + 2;
            // 获取单元格
            $cellA = $sheet->getCell('A'.$n);
            // 设置单元格的值
            $cellA->setValue($v['id']);

            $cellB = $sheet->getCell('B'.$n);
            $cellB->setValue($v['student_name']);

            $cellC = $sheet->getCell('C'.$n);
            $cellC->setValue($v['course_name']);

            $cellD = $sheet->getCell('D'.$n);
            $cellD->setValue($v['name']);

            // 再给表格体设置样式
            $sheet->getStyle('A'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('B'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('C'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('D'.$n)->applyFromArray($styleArrayBody);
        }

        $file_name = '学生成绩列表.xlsx';
        // 实例化导出类
        header('Content-Type:application/vnd.ms-excel');
        header('Content-Disposition:attachment;filename='.$file_name);
        header('Cache-Control:max-age=0');

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }
    // 从 Excel 导入数据
    public function importExcel()
    {
        // 接收文件
        $file = $_FILES['file']['tmp_name'];
        // 创建读操作
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
        // 打开文件、载入excel表格
        $spreadsheet = $reader->load($file);
        // 获取活动工作薄
        $sheet = $spreadsheet->getActiveSheet();

        // 获取总列数
        //$highestColumn = $sheet->getHighestColumn();
        // 获取总行数
        $highestRow = $sheet->getHighestRow();
        // 将列转为数字
        //$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);

        // 存放插入成功的记录
        $successLog = [];
        // 存放插入失败的记录
        $failLog = [];

        // 从第二行开始读取表格数据，循环写入数据库
        for ($i = 2; $i <= $highestRow; $i++) {
            // 第 1 次循环获取第 3 列 第 2 行单元格的值，第 2 次循环获取第 3 列 第 3 行单元格的值
            $student_name = $sheet->getCellByColumnAndRow(2,$i)->getValue();
            $course_name = $sheet->getCellByColumnAndRow(3,$i)->getValue();
            $name = $sheet->getCellByColumnAndRow(4,$i)->getValue();
            $data = [
                'name' => $name,
                'student_id' =>$student_name,
                'course_id' => $course_name,

            ];
            $insert = Db::name('grade')->insert($data);
            if ($insert) {
                $successLog[] = '第'.$i.'条，插入成功';
            } else {
                $failLog[] = '第'.$i.'条，插入失败';
            }
        }
        // 将成功数和失败数返回给 ajax
        return json(['success' => count($successLog), 'fail' => count($failLog)]);
    }
    public function download()
    {
        $spreadsheet = new Spreadsheet();
        // 获取活动单元格
        $sheet = $spreadsheet->getActiveSheet();

        // 获取单元格
        $cellA = $sheet->getCell('A1');
        // 设置单元格的值
        $cellA->setValue('ID');
        // 设置 A 列 列宽
        $sheet->getColumnDimension('A')->setWidth(10);
        // 设置第一行 行高
        $sheet->getRowDimension(1)->setRowHeight(20);

        $cellB = $sheet->getCell('B1');
        $cellB->setValue('学生姓名');
        $sheet->getColumnDimension('B')->setWidth(10);

        $cellC = $sheet->getCell('C1');
        $cellC->setValue('科目名称');
        $sheet->getColumnDimension('C')->setWidth(10);

        $cellD = $sheet->getCell('D1');
        $cellD->setValue('成绩');
        $sheet->getColumnDimension('D')->setWidth(20);


        // 设置样式 标题
        $styleArray = [
            'alignment' => [
                'horizontal' => 'center', //水平居中
                'vertical' => 'center', //垂直居中
            ],
            'font' => [
                'name' => '黑体',
                'bold' => false,
                'size' => 10
            ]
        ];
        // 设置样式 正文
        $styleArrayBody = [
            'alignment' => [
                'horizontal' => 'center', //水平居中
                'vertical' => 'center', //垂直居中
            ],
            'font' => [
                'name' => '宋体',
                'bold' => false,
                'size' => 10
            ]
        ];
        // 应用样式
        $sheet->getStyle('A1')->applyFromArray($styleArray);
        $sheet->getStyle('B1')->applyFromArray($styleArray);
        $sheet->getStyle('C1')->applyFromArray($styleArray);
        $sheet->getStyle('D1')->applyFromArray($styleArray);

        $cellA = $sheet->getCell('A2');
        // 设置单元格的值
        $cellA->setValue(1);

        $cellB = $sheet->getCell('B2');
        $cellB->setValue('张三');

        $cellC = $sheet->getCell('C2');
        $cellC->setValue('语文');
        $cellD = $sheet->getCell('D2');
        $cellD->setValue('86');


        // 再给表格体设置样式
        $sheet->getStyle('A2')->applyFromArray($styleArrayBody);
        $sheet->getStyle('B2')->applyFromArray($styleArrayBody);
        $sheet->getStyle('C2')->applyFromArray($styleArrayBody);
        $sheet->getStyle('D2')->applyFromArray($styleArrayBody);
        $file_name = '学生成绩列表导入模板.xlsx';
        // 实例化导出类，
        header('Content-Type:application/vnd.ms-excel');
        header('Content-Disposition:attachment;filename='.$file_name);
        header('Cache-Control:max-age=0');

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }
}